package pages;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import templates.Template;
import database.DBException;
import database.DBUtils;

/**
 * Servlet implementation class InstrumentsList
 */
public class InstrumentsList extends HttpServlet {
	private static final long serialVersionUID = 1L;

	private static final String pageTitle = "Instruments List";

	private static final String templateName = "instruments_list.xml";
	private static final String rowTemplateName = "instruments_list_row.xml";
	private static final String tblTemplateName = "instruments_list_tbl";

	private static final XSLType xslType = XSLType.Instruments;
	// private int totalPages;
	// private static String xslName = "";

	private static final int rowsOnPage = 10;

	/**
	 * @see HttpServlet#HttpServlet()
	 */
	public InstrumentsList() {
		super();
	}

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doGet(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		int pageNow = 0;
		try {
			pageNow = Integer.parseInt(LMUtils
					.getRequestParameterValueNotEmpty(request, "p"));
		} catch (Exception e) {
		}

		try {
			Template template = new Template(templateName);

			template.addChange("instruments_list_rows",
					loadInstrumentsListRows(request, pageNow));

			template.addChange("pages", LMUtils.pagesText("./InstrumentsList",
					(Integer) request.getAttribute("totalPages"), pageNow));

			String xml = template.applyChanges();
			String xslName = LMUtils.getRequestAttribute(request, "xslName");
			String xsl = XSLT.applyXSL(xml, xslName, xslType);

			Template tableTemplate = new Template(tblTemplateName);

			// xsl select
			tableTemplate.addChange("xsl_select", XSLT
					.getXSLs(xslType, xslName).applyChanges());

			tableTemplate.addChange("instrumensListTable", (xsl != null) ? xsl
					: xml);
			// tableTemplate = LMUtils.adminMenu(tableTemplate);
			response.getWriter().print(
					tableTemplate.createPage(pageTitle, LMUtils
							.getUserLogin(request)));
		} catch (Exception e) {
			ErrorPage.printErrorPage(e.getMessage(), request, response);
		}
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doPost(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		// XSL
		try {
			LMUtils.setRequestAttribute(request, "xslName", LMUtils
					.getRequestParameterValueNotEmpty(request, "xslName"));
		} catch (MissingParameterException e) {
			// no XSL change was asked
		}

		doGet(request, response);
	}

	/**
	 * Fetches instruments info from DB and generates XML containing this info
	 * 
	 * @param request
	 * @param pageNow
	 * @return XML string with instruments
	 * @throws DBException
	 * @throws InternalErrorException
	 */
	private String loadInstrumentsListRows(HttpServletRequest request,
			int pageNow) throws DBException, InternalErrorException {
		Template rowTemplate = new Template(rowTemplateName);
		String rowsText = "";
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		PreparedStatement countStmt = null;
		ResultSet countRS = null;
		try {
			String instrumentsTableName = LMUtils.getContextParameterValue(
					getServletContext(), "db.InstrumentsTableName");
			String typesTableName = LMUtils.getContextParameterValue(
					getServletContext(), "db.TypesTableName");

			conn = DBUtils.getConnection();
			String countQuery = "SELECT COUNT(A.`id`) ";
			String query = "SELECT A.`id`, B.`name`, A.`Name` as `instrumentName`, A.`timeSlot`, A.`isActive`";
			String whereQuery = " FROM `" + instrumentsTableName + "` as A, `"
					+ typesTableName + "` as B" + " WHERE A.`typeID` = B.`id`";
			countQuery += whereQuery + ";";
			query += whereQuery + " ORDER BY A.`id` LIMIT " + pageNow
					* rowsOnPage + ", " + rowsOnPage + ";";

			stmt = conn.prepareStatement(query);
			rs = stmt.executeQuery();
			while (rs.next()) {
				rowTemplate.addChange("instrumentID", rs.getString("id"));
				rowTemplate.addChange("typeName", rs.getString("name"));
				rowTemplate.addChange("name", rs.getString("instrumentName"));
				rowTemplate.addChange("timeSlot", rs.getString("timeSlot"));
				boolean isActive = rs.getBoolean("isActive");
				rowTemplate.addChange("isActive", isActive ? "1" : "0");

				if (isActive) {
					rowTemplate.addChange("activeColor", "green");
				} else {
					rowTemplate.addChange("activeColor", "red");
				}
				rowsText += rowTemplate.applyChanges();
			}

			// total pages
			countStmt = conn.prepareStatement(countQuery);
			countRS = countStmt.executeQuery();
			countRS.next();
			request.setAttribute("totalPages", (int) Math.ceil(countRS
					.getFloat(1)
					/ rowsOnPage));

			return rowsText;
		} catch (SQLException e) {
			throw new DBException("Can't load Instruments List: "
					+ e.getMessage(), e);
		} finally {
			DBUtils.close(countRS);
			DBUtils.close(countStmt);
			DBUtils.close(rs);
			DBUtils.close(stmt);
			DBUtils.close(conn);
		}
	}
}
